package model;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// 完成对 User 的数据库操作
public class UserDao {
    // C R U D
    public boolean add(User user){
        String sql = "insert into user values (null,?,?)";
        Connection connection = DBUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement =  connection.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setString(2,user.getPassword());
            // 执行sql
            int ret = statement.executeUpdate();
            if(ret != 1){// 插入失败
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {// 释放连接
            DBUtil.close(connection,statement,null);
        }
        return true;
    }

    public User selectByName(String name)  {
        // 数据库连接
        Connection connection = DBUtil.getConnection();
        // 拼装sql
        String sql = "select * from user where name = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement =  connection.prepareStatement(sql);
            statement.setString(1,name);
            resultSet = statement.executeQuery();
            // 期待结果集中只有一个结果 : name --> unique;
            if(resultSet.next()){
                User user = new User();
                user.setUserId(resultSet.getInt("userId"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    public User selectById(int id) {
        // 数据库连接
        Connection connection = DBUtil.getConnection();
        // 拼装sql
        String sql = "select * from user where userId = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement =  connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();

            // 期待结果集中只有一个结果 : name --> unique;
            if(resultSet.next()){
                User user = new User();
                user.setUserId(resultSet.getInt("userId"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }
}
